using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using JoinViewProj;

// added by C.Chee
using MySql.Data.MySqlClient; 

namespace WebMenu
{
    public partial class CM_byFood : System.Web.UI.Page
    {
        String myConnectionString;
        MySqlDataReader dataReader;
        MySqlConnection myConn;
        private const string ASCENDING = " ASC";
        private const string DESCENDING = " DESC";
        JoinView jvShop;

        protected void myInit()
        {
            //myConnectionString = "Database=dbcheckmenu;Data Source=localhost;User Id=checkmenu;Password=mysql";
            //myConnectionString = "Database=test;Data Source=localhost;User Id=root;Password=mysql";
            string sDBname = "";
            string sDBuser = "";
            string sDBpass = "";
            string sDBIPaddress = "";
            Config config = new Config();
            bool btest = false;
            config.readConfig(ref sDBname, ref sDBuser, ref sDBpass, ref sDBIPaddress, ref btest);
            myConnectionString = "Database=" + sDBname + ";Data Source=" + sDBIPaddress + ";User Id=" + sDBuser + ";Password=" + sDBpass + "";

            myConn = new MySqlConnection(myConnectionString);
            myConn.Open();
            tb_Messages.Visible = true;
            //tb_Messages.Text = myConnectionString;  // put in page_load so that only show for first time
        }

        protected void myFinal()
        {
            // Close the connection when done with it.
            myConn.Close();
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            
            string cuisineSelect = "";
            string categorySelect;
            string foodSelect;
            this.myInit();

            if (this.IsPostBack == false)  // First Time run
            {
                this.fillCuisineFDropList();
                this.fillCategoryDropList("All");
                this.fillFoodDropList(categoryDropList.SelectedValue, cuisineFDropList.SelectedValue);
                this.hidLatitude.Value = "";
                this.hidLongitude.Value = "";
                this.hidShopDetails.Value = "";
            }
            else       // subsequent page loads
            {
                //cuisineSelect = cuisineFDropList.SelectedValue;
                //categorySelect = categoryDropList.SelectedValue;
                //this.fillCategoryDropList(cuisineSelect );
                //this.fillFoodDropList(categorySelect, cuisineSelect); 
                //butShopUpdate.Visible = false;
            }
            //tb_Messages.Text = cuisineFDropList.SelectedValue;

            //this.updateShop(shopDropList.SelectedValue);
//            this.updateMenu(gvMenu);
            this.updateShopGV(gvShop, cuisineFDropList.SelectedValue, categoryDropList.SelectedValue, foodDropList.SelectedValue);
            this.myFinal();
        }


        protected void fillCuisineFDropList()
        {
            string mySelectQuery = "SELECT  distinct (cuisine) FROM shop ORDER BY CUISINE";
            MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConn);

            MySqlDataAdapter myAdapt = new MySqlDataAdapter(myCommand);
            DataSet ds = new DataSet();
            myAdapt.Fill(ds, "Cuisine");

            // Add extra rows
            DataRow rowNew = null;
            rowNew = ds.Tables["Cuisine"].NewRow();
            rowNew["cuisine"] = "All";
            ds.Tables["Cuisine"].Rows.InsertAt(rowNew, 0);

            //Bind
            cuisineFDropList.DataSource = ds.Tables["Cuisine"];
            cuisineFDropList.DataTextField = "cuisine";
            cuisineFDropList.DataValueField = "cuisine";
            cuisineFDropList.DataBind();

        }

        protected void fillCategoryDropList(string sCuisine)
        {
            tb_Messages.Text += sCuisine;
            string mySelectQuery;

            if (String.Compare(sCuisine, "All") == 0) {
                mySelectQuery = "SELECT  distinct (category) FROM foodmenu order by category";
            }
            else {
                mySelectQuery = "SELECT  distinct (category) FROM foodmenu where shopID in (" +
                                    "SELECT shopID from shop where cuisine = '" + sCuisine + "')" +
                                    "order by category";
            }

            MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConn);

            MySqlDataAdapter myAdapt = new MySqlDataAdapter(myCommand);
            DataSet ds = new DataSet();
            myAdapt.Fill(ds, "Category");

            // Add extra rows
            DataRow rowNew = null;
            rowNew = ds.Tables["Category"].NewRow();
            rowNew["category"] = "All";
            ds.Tables["Category"].Rows.InsertAt(rowNew, 0);

            //Bind
            categoryDropList.DataSource = ds.Tables["Category"];
            categoryDropList.DataTextField = "category";
            categoryDropList.DataValueField = "category";
            categoryDropList.DataBind();

        }

        protected void fillFoodDropList(string sCategory, string sCuisine) 
        {
//            this.updateFoodname("");
            string myInsertQuery = null;
            if (sCategory.Equals("All") && sCuisine.Equals("All")) {
                myInsertQuery = "SELECT DISTINCT foodname FROM foodmenu ORDER BY foodname";
            }
            else if (sCategory.Equals("All")) {
                myInsertQuery = "SELECT DISTINCT foodname FROM foodmenu WHERE " +
                                "shopID in (SELECT shopID FROM shop WHERE cuisine = '" + sCuisine + "') ORDER BY foodname"; 
            }
            else if (sCuisine.Equals("All"))  {
                myInsertQuery = "SELECT DISTINCT foodname FROM foodmenu WHERE category = '" + sCategory + "' ORDER BY foodname";
            }
            else {
                myInsertQuery = "SELECT DISTINCT foodname FROM foodmenu WHERE " + 
                                "shopID in (SELECT shopID FROM shop WHERE cuisine = '" + sCuisine + "') " +
                                "AND category = '" + sCategory + "' ORDER BY foodname";                 
            }

            MySqlCommand myCommand = new MySqlCommand(myInsertQuery, myConn);

            MySqlDataAdapter myAdapt = new MySqlDataAdapter(myCommand);
            DataSet ds = new DataSet();
            myAdapt.Fill(ds, "Foodname");

            int iCount = ds.Tables["Foodname"].Rows.Count;

            foodDropList.DataSource = ds.Tables["Foodname"];
            foodDropList.DataTextField = "foodname";
            foodDropList.DataValueField = "foodname";
            foodDropList.DataBind();

            foodDropList.AppendDataBoundItems = true;
            if (iCount < 1)
            {
                ListItem li = new ListItem("-none-", "");
                foodDropList.Items.Add(li);
            }
            else
            {
                ListItem li = new ListItem("-select-", "");
                foodDropList.Items.Insert(0, li);  //.Add(li);            
            }
            foodDropList.AppendDataBoundItems = false;
        } // end fillFoodDropList



        /// <summary>
        /// Listener for the Suburb drop down list
        /// Page_Load is automatically called first before this, hence need to explicitly call 
        /// Page_Load again to update/clear page.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void cuisineFDropList_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.fillCategoryDropList(cuisineFDropList.SelectedValue);
            this.fillFoodDropList(categoryDropList.SelectedValue, cuisineFDropList.SelectedValue); 
            this.Page_Load(sender, e);
        }

        protected void categoryDropList_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.fillFoodDropList(categoryDropList.SelectedValue, cuisineFDropList.SelectedValue); 
            this.Page_Load(sender, e);
        }

        protected void foodDropList_SelectedIndexChanged(object sender, EventArgs e)
        {   // Create shop list with the following fields:
            // name, address, suburb, state, pcode, phone from table SHOP
            this.updateShopGV(gvShop, cuisineFDropList.SelectedValue,
                            categoryDropList.SelectedValue,
                            foodDropList.SelectedValue);
        }

        protected void updateShopGV(GridView gv, string sCuisine, string sCategory, string sFood)
        {
            string mySelectQuery = null;
            tb_Messages.Text = sCuisine + " " + sCategory + " " + sFood;
            //select shopID from foodmenu where foodname = 'Bombay Beef'
            //select name, address, suburb, state, pcode, phone from shop where shopID in (9, 10)

            mySelectQuery = "SELECT name, address, suburb, state, pcode, phone, price FROM foodmenu INNER JOIN shop " +
            "on shop.shopID = foodmenu.shopID WHERE foodname = '" + sFood + "'";

            MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConn);

            MySqlDataAdapter myAdapt = new MySqlDataAdapter(myCommand);
            DataSet ds = new DataSet();
            myAdapt.Fill(ds, "ShopDetails");
            
            int nShops;
            nShops = ds.Tables[0].Rows.Count;
            if (nShops > 1)
            {
                tb_Messages.Text = nShops + " shops found ";
            }
            else {
                tb_Messages.Text = nShops + " shop found ";
            }

            if (nShops > 0)
            {
                gv.AutoGenerateColumns = true;
                gv.DataSource = ds.Tables["ShopDetails"]; //jvShop;
            }
            else
            {
                gv.DataSource = null;
            }
            //gv.Columns[2].Visible = false;
            gv.DataBind();
        }

        #region gridview manipulation
        // Event:
        protected void gvShop_Sorting(object sender, GridViewSortEventArgs e)
        {
            string sortExpression = e.SortExpression;
            string strSort = null;
            if (gvSortDirection == SortDirection.Ascending)
            {
                gvSortDirection = SortDirection.Descending;
                //SortJVShop(sortExpression, DESCENDING, gvShop);
                strSort = sortExpression + " " + DESCENDING;
            }
            else
            {
                gvSortDirection = SortDirection.Ascending;
                //SortJVShop(sortExpression, ASCENDING, gvShop);
                strSort = sortExpression + " " + ASCENDING;
            }
            DataTable dc1 = (DataTable)gvShop.DataSource;
            dc1.DefaultView.Sort = strSort;
            gvShop.DataBind();
        }

        public SortDirection gvSortDirection
        {
            get
            {
                if (ViewState["sortDirection"] == null)
                    ViewState["sortDirection"] = SortDirection.Ascending;
                return (SortDirection)ViewState["sortDirection"];
            }
            set { ViewState["sortDirection"] = value; }
        }
        private void SortJVShop(string sortExpression, string direction, GridView gv)
        {
            if (jvShop != null)
            {
                jvShop.Sort = sortExpression + direction;
                gv.DataSource = jvShop;
            }
            gv.DataBind();
        }

        // Event:
        protected void gvShop_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType != DataControlRowType.DataRow) return;
            GridView gv = (GridView)sender;
            //e.Row.Cells[0].Width = 100;   // Free Size
            e.Row.Cells[1].Width = 100;     //name  LINK BUTTON
            e.Row.Cells[2].Width = 100;     //name
            //e.Row.Cells[2].Visible = false;
            
            e.Row.Cells[3].Width = 300;     //address
            e.Row.Cells[4].Width = 100;     //suburb
            e.Row.Cells[5].Width = 50;      //state
            e.Row.Cells[6].Width = 50;      //pcode
            e.Row.Cells[7].Width = 150;     //phone
            e.Row.Cells[8].Width = 50;      //price
        }

        // Event:
        protected void gvShop_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            gvShop.PageIndex = e.NewPageIndex;
            gvShop.DataBind();
        }

        protected void lnk_ShopName_Click(object sender, EventArgs e)
        {
            System.Web.UI.WebControls.LinkButton linkBut;
            linkBut = (LinkButton)sender;
            string sShopName = linkBut.CommandArgument;
            this.publishShopPosition(sShopName);
            //tb_Messages.Text += " link of shop clicked is " + linkBut.CommandArgument;
            //tb_Messages.Text += hidLatitude.Value;
            if (!Page.ClientScript.IsStartupScriptRegistered("alert"))
            {
                Page.ClientScript.RegisterStartupScript
                    (this.GetType(), "alert", "drawGmap();", true);
            }

        }

        protected void LinkButton1_Click(object sender, EventArgs e)
        {

        }


        #endregion

        private void publishShopPosition(string sShop)
        {
            string mySelectQuery = "SELECT  * FROM shop where name = '" + sShop + "'";
            MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConn);
            MySqlDataAdapter myAdapt = new MySqlDataAdapter(myCommand);
            DataSet ds = new DataSet();
            myAdapt.Fill(ds, "shopPosn");
            DataRow drr = ds.Tables["shopPosn"].Rows[0]; //.Columns["latitude"].ToString();
            string sShopName;
            sShopName = (string)drr["name"];
            string sShopAddress = (string)drr["address"] + "\r\n" + (string)drr["suburb"] + (string)drr["state"];
            hidShopDetails.Value = sShopName; // + " \r\n" + sShopAddress;
            hidLatitude.Value = (string)drr["latitude"];
            hidLongitude.Value = (string)drr["longitude"];
        }



    }
}
